# 课堂作业：
# 1、练习Pymysq连接数据库，使用自己的数据库做连接
# 2、练习：
# 2.1、通过python连接pymysql数据库，封装类方法
# 2.1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# 2.1.2、查询全部的数据
# 2.1.3、删除指定一条数据
# 2.1.4、封装批量删除方法，可以删除多条
# 1.5、可以指定增加单独一条数据到表里
# 以上每个操作步骤要写日志信息到log.txt里
import pymysql,random
from faker import Faker
from datetime import datetime
USER='root'
PASSWORD='123456'
HOST='8.148.229.201'
DATABASE='jiac'
faker = Faker(locale = 'zh_cn')
class Student:
    def connect(self):
        self.db = pymysql.connect(user= USER,password=PASSWORD,host=HOST,database=DATABASE,cursorclass =
        pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
        str1 = f"当前时间为：{datetime.now()},连接数据库！"
        with open("log.txt",'a',encoding='utf-8') as f:
            f.write(str1 + "\n")
    def close(self):
        self.cursor.close()
        self.db.close()
        str1 = f"当前时间为：{datetime.now()},关闭数据库！"
        with open("log.txt", 'a', encoding='utf-8') as f:
            f.write(str1 + "\n")
    def insert_100_row(self):
        content_list = []
        self.connect()
        for i in range(100):
            name = faker.name()
            age = random.randint(19,60)
            address = faker.address()
            ssn = faker.ssn(min_age=18,max_age=60)
            str1 = f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')"
            self.cursor.execute(str1)
            self.db.commit()
            str2 =  f"当前时间为：{datetime.now()}"+f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')" + "\n"
            print(f"{str1}插入成功！")
            content_list.append(str2)
        with open("log.txt",'a',encoding='utf-8') as f:
            f.writelines(content_list)
        self.close()

    def searchAll(self):
        self.connect()
        self.cursor.execute("select * from student2")
        result = self.cursor.fetchall()
        for index,value in enumerate(result):
            print(f"第{index+1}条的数据为：{value}")
        else:
            print("输出完毕!")
        with open("log.txt",'a',encoding='utf-8') as f:
            f.write(f"当前时间：{datetime.now()}，执行了查询操作！")
        self.close()
    def delete_row(self,name):
        self.connect()
        str1 = f"delete from student2 where name='{name}'"
        self.cursor.execute(str1)
        self.db.commit()
        with open('log.txt','a',encoding='utf-8') as f:
            f.write(f"当前时间：{datetime.now()}，删除：{str1}  成功！" + "\n")
        self.close()
    def delete_row_(self,*names):
        content_list = []
        self.connect()
        for name in names:
            str1 = f"delete from student2 where name='{name}'"
            self.cursor.execute(str1)
            self.db.commit()
            print(f"删除：{name}数据成功！")
            str2 = f"当前时间：{datetime.now()}"+f"delete from student2 where name={name}" +"删除成功！" +"\n"
            content_list.append(str2)
        else:
            print("全部删除完毕！")
        with open('log.txt','a',encoding='utf-8') as f:
            f.writelines(content_list)
        self.close()

    def insert_one_row(self,name,age,address,ssn):
        self.connect()
        str1 = f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')"
        self.cursor.execute(str1)
        self.db.commit()
        print(f"{str1}新增数据成功！")
        str2 =f"当前时间：{datetime.now()}" +f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')" +"  新增成功！\n"
        with open('log.txt','a',encoding='utf-8') as f:
            f.write(str2)
        self.close()
if __name__ == '__main__':
    s1 = Student()
    # s1.insert_100_row()
    # s1.searchAll()
    # s1.delete_row("易成")
    # s1.delete_row_("张丽华","张倩") #
    s1.insert_one_row("z张鑫",19,"沈阳市","22029788777892")